﻿//**************************************************************************//
// Copyright (C) Abdul Rasheed. All rights Reserved.                        //
// rasheedat.blogspot.com                                                   //
//**************************************************************************//
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using PPHR.Common;
using PPHR.DataAccess;

namespace PPHR.DataLogic
{
    /// <summary>
    /// All the visit detail related queries will be stored here
    /// </summary>
    public class VisitDataLogic
    {
        DBConnection dbCon = new DBConnection();
        CommonDataLogic commonLogic = new CommonDataLogic();
        
        /// <summary>
        /// Get all the visit list
        /// </summary>
        /// <returns></returns>
        public DataTable VisitList()
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT VisitDetail.PatientID, VisitDetail.VisitNumber, VisitDetail.VisitDate, MasterCodes.Description As VisitType, VisitDetail.ConsultantName, VisitDetail.FacilityName";
                sql += " FROM VisitDetail INNER JOIN";
                sql += "      MasterCodes ON VisitDetail.VisitType = MasterCodes.Code";
                sql += " WHERE (MasterCodes.CodeGroup = 'VISITTYP')";
                sql += " ORDER BY VisitDetail.VisitDate DESC";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }

        /// <summary>
        /// Get visit header
        /// </summary>
        /// <param name="PV"></param>
        /// <returns></returns>
        public DataTable GetVisitHeader(VisitData PV)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT PatientID, VisitNumber, VisitDate, VisitType, ConsultantName, FacilityName, VisitDetail";
                sql += " FROM VisitDetail Where VisitNumber ='" + PV.VisitNumber + "'";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }

        /// <summary>
        /// Get visit prescription
        /// </summary>
        /// <param name="PV"></param>
        /// <returns></returns>
        public DataTable GetVisitPrescription(VisitData PV)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT DocumentID, Note, PrescriptionDocument";
                sql += " FROM VisitPrescription Where VisitNumber ='" + PV.VisitNumber + "'";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }

        /// <summary>
        /// Get visit doctor voice detail
        /// </summary>
        /// <param name="PV"></param>
        /// <returns></returns>
        public DataTable GetVisitDrVoice(VisitData PV)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT VoiceID, Note, DoctorVoiceComment";
                sql += " FROM VisitDoctorVoice Where VisitID ='" + PV.VisitNumber + "'";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }
        
        /// <summary>
        /// Get visit additional information
        /// </summary>
        /// <param name="PV"></param>
        /// <returns></returns>
        public DataTable GetVisitAdditionalInfo(VisitData PV)
        {
            DataTable dt = null;
            try
            {
                string sql = "SELECT AdditionalInfoID, Note, AdditionalInformationDocument";
                sql += " FROM VisitAdditionalInfo Where VisitID ='" + PV.VisitNumber + "'";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }

        /// <summary>
        /// Add visit detail in DB
        /// </summary>
        /// <param name="PV"></param>
        public void AddVisit(VisitData PV)
        {
            string sql = string.Empty;
            int result = -1;
            //Insert Header
            try
            {
                PV.VisitNumber = commonLogic.KeyNumber("VISIT");
                sql = "Insert into VisitDetail Values(";
                sql += "'" + PV.PatientID + "', ";
                sql += "'" + PV.VisitNumber + "', ";
                sql += "'" + PV.VisitDate + "', ";
                sql += "'" + PV.VisitType + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PV.ConsultantName) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PV.FacilityName) + "', ";
                sql += "'" + commonLogic.replaceInjectionString(PV.VisitDetail) + "',";
                sql += "'" + DateTime.Now.ToString() + "', ";
                sql += "'" + DateTime.Now.ToString() + "'";
                sql += ")";
                result = dbCon.ExecuteNonQuery(sql);

                // Prescription
                foreach (Prescription pres in PV.Prescription)
                {
                    sql = "Insert into VisitPrescription Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITPRES") + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(pres.Note) + "', ";
                    sql += "'" + pres.PrescriptionDocument + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }

                // Voice
                foreach (DoctorVoice drVoice in PV.DrVoice)
                {
                    sql = "Insert into VisitDoctorVoice Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITDRVOICE") + "', ";
                    sql += "'" + drVoice.DoctorVoiceComment + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(drVoice.Note) + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }

                // Additional Info
                foreach (AdditionalInfo additionalInfo in PV.AdditionalInfo)
                {
                    sql = "Insert into VisitAdditionalInfo Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITADDITION") + "', ";
                    sql += "'" + additionalInfo.AdditionalInformationDocument + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(additionalInfo.Note) + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
        }

        /// <summary>
        /// Update Visit detail
        /// </summary>
        /// <param name="PV"></param>
        public void UpdateVisitDetail(VisitData PV)
        {
            string sql = string.Empty;
            int result = -1;
            try
            {
                //Update Header
                sql = "Update VisitDetail Set ";
                sql += "VisitDate = '" + PV.VisitDate + "', ";
                sql += "VisitType = '" + PV.VisitType + "', ";
                sql += "ConsultantName = '" + commonLogic.replaceInjectionString(PV.ConsultantName) + "', ";
                sql += "FacilityName = '" + commonLogic.replaceInjectionString(PV.FacilityName) + "', ";
                sql += "VisitDetail = '" + commonLogic.replaceInjectionString(PV.VisitDetail) + "', ";
                sql += "UpdatedOn= '" + DateTime.Now.ToString() + "' ";
                sql += "Where VisitNumber = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);

                // Prescription
                sql = "Delete from VisitPrescription ";
                sql += "Where VisitNumber = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);
                foreach (Prescription pres in PV.Prescription)
                {
                    sql = "Insert into VisitPrescription Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITPRES") + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(pres.Note) + "', ";
                    sql += "'" + pres.PrescriptionDocument + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }
                // Voice
                sql = "Delete from VisitDoctorVoice ";
                sql += "Where VisitID = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);
                foreach (DoctorVoice drVoice in PV.DrVoice)
                {
                    sql = "Insert into VisitDoctorVoice Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITDRVOICE") + "', ";
                    sql += "'" + drVoice.DoctorVoiceComment + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(drVoice.Note) + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }

                // Additional Info
                sql = "Delete from VisitAdditionalInfo  ";
                sql += "Where VisitID = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);
                foreach (AdditionalInfo additionalInfo in PV.AdditionalInfo)
                {
                    sql = "Insert into VisitAdditionalInfo Values(";
                    sql += "'" + PV.VisitNumber + "', ";
                    sql += "'" + commonLogic.KeyNumber("VISITADDITION") + "', ";
                    sql += "'" + additionalInfo.AdditionalInformationDocument + "', ";
                    sql += "'" + commonLogic.replaceInjectionString(additionalInfo.Note) + "', ";
                    sql += "'" + DateTime.Now.ToString() + "', ";
                    sql += "'" + DateTime.Now.ToString() + "'";
                    sql += ")";
                    result = dbCon.ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
        }

        /// <summary>
        /// Delete visit detail
        /// </summary>
        /// <param name="PV">Filter criteria</param>
        public void DeleteVisit(VisitData PV)
        {
            string sql = string.Empty;
            int result = -1;
            try
            {
                //Update Header
                sql = "Delete from VisitDetail ";
                sql += "Where VisitNumber = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);

                // Prescription
                sql = "Delete from VisitPrescription ";
                sql += "Where VisitNumber = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);

                // Voice
                sql = "Delete from VisitDoctorVoice ";
                sql += "Where VisitID = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);

                // Additional Info
                sql = "Delete from VisitAdditionalInfo  ";
                sql += "Where VisitID = '" + PV.VisitNumber.ToString() + "' ";
                result = dbCon.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }

        }
    }
}
